﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using System.Data;
using KPIS.GERP.GHRMS.MODEL;
using System.Configuration;

namespace KPIS.GERP.GHRMS.DAL
{
    public class TrainingScholarshipsRequestsDAL
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        ConvertNullable cn = new ConvertNullable();

        public DataSet GetDatacbEduInstitutes(Nullable<int> edu_ins_seq)
        {
            DataSet ds = new DataSet();
            dbManager.Open();
            dbManager.CreateParameters(2);
            dbManager.AddParameters(0, "@edu_ins_seq", edu_ins_seq);
            dbManager.AddParameters(1, "@record_status", SystemInfo._ActiveRecordStatus);

            string sql = "SELECT mas_edu_institutes.edu_ins_seq as Sequence"
                        + ", mas_edu_institutes.edu_ins_name as Name"
                        + " FROM mas_edu_institutes"
                        + " WHERE mas_edu_institutes.record_status = @record_status"
                        + (edu_ins_seq > 0 ? " AND mas_edu_institutes.edu_ins_seq = @edu_ins_seq " : "");

            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }

        //public DataSet GetDatacbScholar(Nullable<int> edu_ins_seq)
        //{
        //    DataSet ds = new DataSet();
        //    dbManager.Open();
        //    dbManager.CreateParameters(2);
        //    dbManager.AddParameters(0, "@edu_ins_seq", edu_ins_seq);
        //    dbManager.AddParameters(1, "@record_status", SystemInfo._ActiveRecordStatus);

        //    string sql = "SELECT mas_edu_institutes.edu_ins_seq as Sequence"
        //                + ", mas_edu_institutes.edu_ins_name as Name"
        //                + " FROM mas_edu_institutes"
        //                + " WHERE mas_edu_institutes.record_status = @record_status"
        //                + (edu_ins_seq > 0 ? " AND mas_edu_institutes.edu_ins_seq = @edu_ins_seq " : "");

        //    ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
        //    dbManager.Dispose();
        //    return ds;
        //}
        public DataSet GetDataSchReq(Nullable<int> emp_seq, Nullable<int> seq)
        {
            DataSet ds = new DataSet();
            dbManager.Open();
            dbManager.CreateParameters(3);
            dbManager.AddParameters(0, "@emp_seq", emp_seq);
            dbManager.AddParameters(1, "@seq", seq);
            dbManager.AddParameters(2, "@record_status", SystemInfo._ActiveRecordStatus);

            string sql = "SELECT sr.tran_seq"
                    + ", sr.tran_date"
                    + ", sr.emp_seq"
                    + ", sr.date_start"
                    + ", sr.edu_major_seq"
                    + ", sr.duration_month"
                    + ", sr.duration_year"
                    + ", st.scholar_type_descr"
                    + ", st.scholar_local"
                    + ", ss.date_sign_start"
                    + ", ss.closed"
                    + ", ss.date_sign_end"
                    + ", sr.response_type_seq"
                    + ", sr.scholar_seq"
                    + ", sg.scholar_group_seq"
                    + ", em.edu_major_name"
                    + ", CASE WHEN sr.response_type_seq = 0 then 'ไม่อนุมัติ'"
                    + " WHEN sr.response_type_seq = 1 then 'อนุมัติ' ELSE 'อยู่ระหว่างการพิจารณา' END AS Approve"
                    + ", CASE WHEN ss.closed not like 0 THEN 'ปิด' ELSE 'เปิด' END AS Status"
                    + ", CASE WHEN st.scholar_local not like 0 THEN CONCAT(st.scholar_type_descr,'(ในประเทศ)') ELSE CONCAT(st.scholar_type_descr,'(ต่างประเทศ)') END AS Name"
                    + " FROM hrm_scholarships_requests sr "
                    + " Left Join hrm_scholarships ss ON sr.scholar_seq = ss.scholar_seq"
                    + " Left Join hrm_scholarship_types st ON ss.scholar_type_seq = st.scholar_type_seq"
                    + " Left Join mas_edu_majors em ON sr.edu_major_seq = em.edu_major_seq"
                    + " Left Join hrm_scholarships_groups sg ON sr.scholar_group_seq = sg.scholar_group_seq"
                    + " WHERE sr.record_status = @record_status"
                    + (emp_seq > 0 ? " AND sr.emp_seq = @emp_seq " : "")
                    + (seq > 0 ? " AND sr.tran_seq = @seq " : "");

                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }

        public void InsertSchReq(int emp_seq, int scholar_seq, int edu_ins_seq, int edu_major_seq,  string teacher_name, string response_no, DateTime response_date, DateTime date_start, int duration_month
                            , int duration_year,int amount_request, Nullable<int> created_by)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(15);

                dbManager.AddParameters(0, "@tran_date", DateTime.Now);
                dbManager.AddParameters(1, "@emp_seq", emp_seq);
                dbManager.AddParameters(2, "@scholar_seq", scholar_seq);
                dbManager.AddParameters(3, "@edu_ins_seq", edu_ins_seq);
                dbManager.AddParameters(4, "@edu_major_seq", edu_major_seq);
                //dbManager.AddParameters(5, "@training_cur_seq", training_cur_seq);
                dbManager.AddParameters(5, "@created_when", DateTime.Now);
                dbManager.AddParameters(6, "@teacher_name", teacher_name);
                dbManager.AddParameters(7, "@response_no", response_no);
                dbManager.AddParameters(8, "@response_date", response_date);
                dbManager.AddParameters(9, "@date_start", date_start);
                dbManager.AddParameters(10, "@duration_month", duration_month);
                dbManager.AddParameters(11, "@duration_year", duration_year);
                dbManager.AddParameters(12, "@amount_request", amount_request);
                dbManager.AddParameters(13, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(14, "@created_by", created_by);
                //dbManager.AddParameters(15, "@created_when", DateTime.Now);


                strSQL = "INSERT hrm_scholarships_requests"
                    + "(tran_date"
                    + ", emp_seq"
                    + ", scholar_seq"
                    + ", edu_ins_seq"
                    + ", edu_major_seq"
                    //+ ", training_cur_seq"
                    + ", teacher_name"
                    + ", date_start"
                    + ", response_no"
                    + ", response_date"
                    + ", duration_month"
                    + ", duration_year"
                    + ", amount_request"
                    + ", record_status"
                    + ", created_by"
                    + ", created_when)"
                    + " VALUES (@tran_date"
                    + ", @emp_seq"
                    + ", @scholar_seq"
                    + ", @edu_ins_seq"
                    + ", @edu_major_seq"
                    //+ ", @training_cur_seq"
                    + ", @teacher_name"
                    + ", @date_start"
                    + ", @response_no"
                    + ", @response_date"
                    + ", @duration_month"
                    + ", @duration_year"
                    + ", @amount_request"
                    + ", @record_status"
                    + ", @created_by"
                    + ", @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

    }
}
